--    Author    : ChenErHao
--    Name      : EDW.LOAF.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-10  ChenErHao       1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.LOAF PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 
SELECT 
  FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE               -- 跑批日期
  ,DATA_ORG_CODE                                                                   -- 金融机构编码
  ,CASE WHEN CUSTOMER_TYPE='0' THEN '2' ELSE CUSTOMER_TYPE END AS CUSTOMER_TYPE         -- 客户类型
  ,CUSTOMER_ID                                                                     -- 借款人代码
  ,NVL(LPIC,'9999')                                                                -- 贷款主体行业类别
  ,REGIST_ADD_CODE                                                                 -- 借款人注册地编码
  ,NVL(ENTERPRISE_INVESTOR,'9999')                                                 -- 企业出资人经济成分
  ,NVL(ENTERPRISE_SCALE,'9999') ENTERPRISE_SCALE                                   -- 企业规模
  ,LOAN_IOUS_CODE                                                                  -- 贷款借据编码
  ,PRODUCT_CATEGORY                                                                -- 产品类别
  ,LOANS_ACTUALLY                                                                  -- 贷款实际投向
  ,LENDING_DATE                                                                    -- 贷款发放日期
  ,LOAN_DUE_DATE                                                                   -- 贷款到期日期
  ,LOAN_ACTUAL_END_DATE                                                            -- 贷款实际终止日期
  ,LOAN_CUR_TYPE                                                                   -- 贷款币种
  ,LOAN_OCCURRENCE                                                                 -- 贷款余额
  ,RATE_FIXED_FLOAT                                                                -- 利率是否固定
  ,RATE_LEVEL                                                                      -- 利率水平
  ,GUARANTY_STYLE                                                                  -- 贷款担保方式
  ,LOAN_STATUS                                                                     -- 贷款状态
  ,NVL(LOAN_GRANT_RECOVER,'9999')                                                  -- 贷款发放收回标志
  ,CASE WHEN LENDING_DATE='' OR LENDING_DATE IS NULL 
            OR LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL THEN 99
        WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=6  THEN 10
        WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=12 THEN 20
        WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=36 THEN 30
        WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=60 THEN 40
        WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=120 THEN 50
        WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)>120 THEN 60
     ELSE 9999
    END LOANS_PERIOD                                                                                  -- 贷款期限
  ,NVL(T3.AREA_NO_ID_1,'9999') AS OWNS_PROV_ID                                                               -- 省
  ,NVL(T3.AREA_NO_ID,'9999') AS OWNS_AREA_ID                                                                 -- 市
  ,NVL(T3.AREA_NO_ID,'9999') AS OWNS_CITY_ID                                                                 -- 县
  ,T2.JRTJ_AREA_CODE                                                                                       -- 大集中地区编码
  ,CASE WHEN T2.ORG_LVL_ID_2='A05' THEN 'A05'
     ELSE T2.ORG_LVL_ID_3
    END AS DATA_ORG_TYPE                                                                                -- 数据机构类型
  ,CASE WHEN LOAN_OCCURRENCE/10000<=5 THEN 'A1'
        WHEN LOAN_OCCURRENCE/10000<=10 THEN 'A2'
        WHEN LOAN_OCCURRENCE/10000<=30 THEN 'A3'
        WHEN LOAN_OCCURRENCE/10000<=50 THEN 'A4'
        WHEN LOAN_OCCURRENCE/10000<=100 THEN 'B1'
        WHEN LOAN_OCCURRENCE/10000<=300 THEN 'B2'
        WHEN LOAN_OCCURRENCE/10000<=500 THEN 'B3'
        WHEN LOAN_OCCURRENCE/10000<=800 THEN 'C1'
        WHEN LOAN_OCCURRENCE/10000<=1000 THEN 'C2'
        WHEN LOAN_OCCURRENCE/10000<=5000 THEN 'C3'
        WHEN LOAN_OCCURRENCE/10000<=10000 THEN 'D'
        WHEN LOAN_OCCURRENCE/10000<=100000 THEN 'E'
        WHEN LOAN_OCCURRENCE/10000>100000 THEN 'F'
        ELSE '9999'
   END AMT_INTERVAL_ID                                                          -- 金额区间
  ,CASE WHEN LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL or LOAN_GRANT_RECOVER = 0 THEN 9999 
        when LOAN_GRANT_RECOVER = 1 then CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,'#V_LINE_DATE#')) 
     else 9999
   END SHENGYU_PERIOD_M                                                                                     --剩余期限
  ,case when RATE_LEVEL<=3 then 10
        when RATE_LEVEL<=5 then 20
        when RATE_LEVEL<=7 then 30
        when RATE_LEVEL<=9 then 40
        when RATE_LEVEL<=11 then 50
        when RATE_LEVEL<=13 then 60
        when RATE_LEVEL > 13  then 70 
      else '' 
    end RATE_LEVEL_QJ                                                                                       --利率区间 
  ,CASE WHEN LENDING_DATE='' OR LENDING_DATE IS NULL OR LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL THEN 9999
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=6  THEN 'A'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=12 THEN 'B'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=18 THEN 'C1'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=24 THEN 'C2'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=30 THEN 'C3'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=36 THEN 'C4'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=42 THEN 'D1'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=48 THEN 'D2'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=54 THEN 'D3'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=60 THEN 'D4'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=66  THEN 'E1'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=72 THEN 'E2'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=78 THEN 'E3'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=84 THEN 'E4'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=90 THEN 'E5'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=96 THEN 'E6'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=102 THEN 'E7'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=108 THEN 'E8'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=114 THEN 'E9'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=120 THEN 'E91'
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)>120 THEN 'F1' 
      ELSE 9999 
   END AS LOANS_PERIOD_CUB                                                                                               --贷款期限
  ,CASE WHEN LENDING_DATE IS NULL OR LENDING_DATE='' OR LOAN_DUE_DATE IS NULL OR  LOAN_DUE_DATE='' OR MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<0 THEN 0
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=6  THEN D.SHORT_SIXM
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=12 THEN D.SHORT_ONEY
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=36 THEN D.LONG_ONEY
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=60 THEN D.LONG_THREEY
      WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)>60  THEN D.LONG_FIVEY
     ELSE 0
   END AS BASE_RATE                                                -- 基准利率
  ,LOAN_OCCURRENCE*RATE_LEVEL AS LOAN_OCCURRENCE_LEVEL                                                             -- 金额加权值
  ,1 as STROKE_COUNT                                                                                       -- 笔数
  ,DATA_SRC_ORG
FROM ODS.LOAF T1
LEFT JOIN DMCODE.T_ORG_BIZ_LVL T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.DATA_ORG_CODE=t2.ORG_ID
LEFT JOIN DMCODE.T_AREA_CODE T3 ON T1.DATA_DATE = '#V_DATA_DATE#' AND T2.AREA_NO_ID = T3.AREA_NO_ID
JOIN DMCODE.T_LOAN_RATE D
WHERE T1.DATA_DATE = '#V_DATA_DATE#'
 AND NVL(T1.LENDING_DATE,'10000101') BETWEEN D.BEGIN_DATE AND D.END_DATE
;
